from django.test import TestCase

# Create your tests here.

# import yaml
# filepath = r'E:\pycharm_projects\myfirstproj\myfirstproj\myappconfig.yaml'
# with open(filepath,'r',encoding='utf8') as f:
#     res = yaml.load(f, Loader=yaml.FullLoader)
#     print(res)
#     print(type(res))
#
# from django.conf import settings
import django
import os

os.environ['DJANGO_SETTINGS_MODULE'] = 'myfirstproj.settings'
django.setup()
# # static_file_path = os.path.join(settings.BASE_DIR,'static')
# #
# # print('static_file_path:', static_file_path)
# # filename = r'/abc.png'
# # filepath = os.path.join(static_file_path, filename)
# # print(filepath)
# print('basedir:',settings.BASE_DIR)
#
# static_filedir = settings.STATIC_URL
# # print('static_filedir',static_filedir)
# print('static_filedir',os.path.join(settings.BASE_DIR,'static'))
# print(settings.STATIC_ROOT)

from juheapp.models import User

# r = User.objects.filter(nickname__contains='方')
# r = User.objects.get(nickname='方寸')
# print(r)
import random


def ranstr(length):
    CHS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
    salt = ''
    for i in range(length):
        salt += random.choice(CHS)
    return salt


# 增删改查

def add_one():
    # 1
    # user = User(openid = 'test_openid', nickname='test_nickname')
    # user.save()

    # 2
    User.objects.create(openid='test_open_id2', nickname='test_nickname2')


def add_batch():
    new_user_list = []
    for i in range(5):
        open_id = ranstr(32)
        nickname = ranstr(10)
        user = User(openid=open_id, nickname=nickname)
        new_user_list.append(user)
    User.objects.bulk_create(new_user_list)


# add_batch()

# 查询  精确查询
def get_one():
    # 找不到就juheapp.models.DoesNotExist: User matching query does not exist.
    user = User.objects.get(openid='test_openid111')
    print(user)


# 数据过滤  模糊查询
def get_filter():
    # bindtap类似对比
    # 属性__哪种类型的模糊查询
    users = User.objects.filter(openid__contains='test')
    # open_id__startswith
    # 大于: open_id__gt(greater than)
    # 小于: open_id__lt(little than)
    # 大于等于：open_id__gte(greater than equal)
    # 小于等于：open_id__lte(little than equal)
    print(users)


# 数据排序
def get_order():
    users = User.objects.order_by('nickname')
    print(users)


# get_order()

# 修改比查询多一个步骤
# 删除比查询多一个步骤

from django.db.models import Value
from django.db.models.functions import Concat


# annotate创建对象的一个属性, Value里可以随便写,引号中如果不是对象中原有属性会报错
def concat_function():
    users = User.objects.filter(openid='test_openid').annotate(
        # open_id=(open_id), nickname=(nickname)
        screen_name=Concat(
            Value('openid='), 'nickname'
        ))
    print(users[0].screen_name)


# concat_function()



# F:对数据本身的不同字段进行操作 如:比较和更新
# def testf():
#     user = User.objects.get(openid='test_openid')
#     F('nickname')+'s'
#     user.save()
# testf()

from juheapp.models import App
from app1.models import Article
from django.db.models.functions import Now

from datetime import datetime,timedelta
dt = datetime(day=1,year=2020,month=3)
print(dt)


def now_function():
    # 当前日期之前发布的所有应用
    # gt >  greater than
    #  lt less than <
    # e equel ==
    articles = Article.objects.filter(publist_date__lt=dt)
    for article in articles:
        print(article)

# now_function()

from django.db.models import Count
from django.db.models.functions import Trunc


def trunc_function():
    # 打印每一天发布的应用数量
    article_per_day = Article.objects.annotate(publish_day=Trunc('publist_date', 'year'))\
        .values('publish_day')\
        .annotate(publish_num=Count('article_id'))
    # article_id, brief_content, content, publish_day, publist_date, title
    for article in article_per_day:
        print('date:', article['publish_day'], ', publish num:', article['publish_num'])


trunc_function()



# def addarticle():
#     Article.objects.create(title='ttt',publist_date=dt)
#
# addarticle()

from django.db.models import Q

# Q：用于构造复杂的查询条件 如：& |操作
def getfilter2():
    users = User.objects.filter(~Q(openid__contains='test'))
    # users = User.objects.filter(Q(openid__contains='test') & Q(nickname__contains='names'))
    # users = User.objects.filter(nickname__contains='test')
    print(users)


# getfilter2()


from django.db.models import F

user = User.objects.get(openid='test_openid')
user.nickname = F('nickname')+'1'
user.save()
print(user.nickname)


# print(type(Article.objects.annotate(publish_day=Trunc('publist_date', 'year'))\
#         ))
#
# from django.db.models.query import QuerySet